﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsBOMDataAccess
    {
        public static DataTable SelectList(string BOMID, string WorkOrderNo, string ProductName, string cpFrom, string cpEnd, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select * from V_BOM o where 1=1 ";
                    if (!string.IsNullOrEmpty(BOMID))
                    {
                        strcmd += " and o.BOMID like @BOMID";
                        dpc.AddWithValue("@BOMID", "%" + BOMID + "%");
                    }
                    if (!string.IsNullOrEmpty(WorkOrderNo))
                    {
                        strcmd += " and o.WorkOrderNo like @WorkOrderNo";
                        dpc.AddWithValue("@WorkOrderNo", "%" + WorkOrderNo + "%");
                    }
                    if (!string.IsNullOrEmpty(ProductName))
                    {
                        strcmd += " and o.ProductName like @ProductName";
                        dpc.AddWithValue("@ProductName", "%" + ProductName + "%");
                    }
                    if (!string.IsNullOrEmpty(cpFrom))
                    {
                        strcmd += " and o.BillDate >= @From";
                        dpc.AddWithValue("@From", cpFrom + " 00:00:00");
                    }
                    if (!string.IsNullOrEmpty(cpEnd))
                    {
                        strcmd += " and o.BillDate <= @End";
                        dpc.AddWithValue("@End", cpEnd + " 23:59:59");
                    }
                    strcmd += " order by o.BillDate desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectDialogProduct(string BOMID, string ProductID, string ProductName, string userid, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select od.*,p.ProductTypeID,pt.ProductTypeName,p.ProductName,
                                        p.Specifications,p.OutSide,p.InSide,p.Unit,p.SafeStock
                                        from BS_OrderDetail od
                                        left join MD_Product p on p.ProductID=od.ProductID
                                        left join MD_ProductType pt on pt.ProductTypeID=p.ProductTypeID
                                        where 1=1 ";
                    if (!string.IsNullOrEmpty(BOMID))
                    {
                        strcmd += " and od.WorkOrderNo not in (select WorkOrderNo from BS_BOM where BOMID<>@BOMID)";
                        dpc.AddWithValue("@BOMID", BOMID);
                    }
                    if (!string.IsNullOrEmpty(ProductID))
                    {
                        strcmd += " and od.ProductID like @ProductID";
                        dpc.AddWithValue("@ProductID", "%" + ProductID + "%");
                    }
                    if (!string.IsNullOrEmpty(ProductName))
                    {
                        strcmd += " and p.ProductName like @ProductName";
                        dpc.AddWithValue("@ProductName", "%" + ProductName + "%");
                    }
                    strcmd += " order by od.ProductID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectDialogMaterial(string MaterialID, string MaterialName, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select m.*,mt.MaterialTypeName,mst.MaterialSmallTypeName from MD_Material m
                                        left join MD_MaterialType mt on mt.MaterialTypeID=m.MaterialTypeID
                                        left join MD_MaterialSmallType mst on mst.MaterialSmallTypeID=m.MaterialSmallTypeID
                                        where 1=1";
                    if (!string.IsNullOrEmpty(MaterialID))
                    {
                        strcmd += " and m.MaterialID like @MaterialID";
                        dpc.AddWithValue("@MaterialID", "%" + MaterialID + "%");
                    }
                    if (!string.IsNullOrEmpty(MaterialName))
                    {
                        strcmd += " and m.MaterialName like @MaterialName";
                        dpc.AddWithValue("@MaterialName", "%" + MaterialName + "%");
                    }
                    strcmd += " order by m.MaterialID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectDialogKnife(string KnifeID, string KnifeName, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select k.*,kt.KnifeTypeName from MD_Knife k
                                        left join MD_KnifeType kt on kt.KnifeTypeID=k.KnifeTypeID
                                        where 1=1";
                    if (!string.IsNullOrEmpty(KnifeID))
                    {
                        strcmd += " and k.KnifeID like @KnifeID";
                        dpc.AddWithValue("@KnifeID", "%" + KnifeID + "%");
                    }
                    if (!string.IsNullOrEmpty(KnifeName))
                    {
                        strcmd += " and k.KnifeName like @KnifeName";
                        dpc.AddWithValue("@KnifeName", "%" + KnifeName + "%");
                    }
                    strcmd += " order by k.KnifeID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetBOMDetailByID(string id)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select * from V_BOM where BOMID=@BOMID";
                    dpc.AddWithValue("@BOMID", id);
                    return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetBOMKnifeByID(string id)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select bk.*,k.KnifeName,k.Specifications,k.Unit,k.BelongCustomer,
                                        k.Factory,k.SafeStock,k.KnifeTypeID,kt.KnifeTypeName,p.ParameterName as KnifeStatusName
                                        from BS_BOMKnife bk
                                        left join MD_Knife k on k.KnifeID=bk.KnifeID
                                        left join MD_KnifeType kt on kt.KnifeTypeID=k.KnifeTypeID
                                        left join SYS_Parameter p on p.ParameterID=bk.KnifeStatus and p.ParameterTypeID='KnifeStatus'
                                        where bk.BOMID=@BOMID";
                    dpc.AddWithValue("@BOMID", id);
                    return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }
    }
}
